IF OBJECT_ID('fGetSegmentLevel') IS NOT NULL 
BEGIN 
    DROP FUNCTION fGetSegmentLevel
    IF OBJECT_ID('fGetSegmentLevel') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION fGetSegmentLevel >>>'
    ELSE 
        PRINT '<<< DROPPED FUNCTION fGetSegmentLevel >>>'
END
GO
CREATE FUNCTION dbo.fGetSegmentLevel
	(
        @SegmentId int = NULL
	)
RETURNS int
AS
BEGIN
    DECLARE
        @SegmentLevel int
    ,   @ParentSegmentId  int

    SET @SegmentLevel = 0

    SELECT @ParentSegmentId = ParentSegmentId FROM Segment WHERE SegmentId = @SegmentId

    WHILE @ParentSegmentId IS NOT NULL
    BEGIN
        SET @SegmentLevel = @SegmentLevel + 1
        SET @SegmentId = @ParentSegmentId
        SELECT @ParentSegmentId = ParentSegmentId FROM Segment WHERE SegmentId = @SegmentId
    END    

RETURN @SegmentLevel
END
GO
GRANT EXECUTE ON fGetSegmentLevel TO public
GO
IF OBJECT_ID('fGetSegmentLevel') IS NOT NULL 
    PRINT '<<< CREATED FUNCTION fGetSegmentLevel >>>' 
ELSE 
    PRINT '<<< FAILED CREATING FUNCTION fGetSegmentLevel >>>' 
GO
 